﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Linq;
using Citi.GocAttributes.Domain.Utils;
using Citi.Import.Util;
using System.IO;
using OfficeOpenXml;

namespace Citi.GocAttributes.Domain
{
    public partial class Goc
    {
        public List<string> ManagedSegmentLevels
        {
            get
            {
                var managedSegmentLevels = new List<string>();

                GetManagedSegmentLevels(ManagedSegment, ref managedSegmentLevels);
                return managedSegmentLevels;
            }
        }

        public List<string> FunctionsLevels
        {
            get
            {
                var functionLevels = new List<string>();
                GetFunctionLevels(Function, ref functionLevels);

                return functionLevels;
            }
        }

        private void GetManagedSegmentLevels(ManagedSegment managedSegment, ref List<string> managedSegmentLevels)
        {
            managedSegmentLevels.Insert(0, managedSegment.Description);

            if (managedSegment.ParentId != null)
                GetManagedSegmentLevels(managedSegment.Parent, ref managedSegmentLevels);
        }

        private void GetFunctionLevels(Function function, ref List<string> functionLevels)
        {
            functionLevels.Insert(0, function.Description);

            if (function.ParentId != null)
                GetFunctionLevels(function.Parent, ref functionLevels);
        }

        #region Import Validation

        private const string GocAttributesColumnName = "GOC";
        private const string GocLongDescriptionColumnName = "GOCDESC";
        private const string StatusColumnName = "GOC_EFF_STATUS";
        private const string UpdatedDateColumnName = "GOC_EFF_DATE";
        private const string SidColumnName = "SID";
        private const string LocalCostCodeColumnName = "LOCALCOSTCD";
        private const string ManagerIdColumnName = "MANAGERID";
        private const string FrsBuColumnName = "C_DSMT_TN_FRSBU";
        private const string FrsOuColumnName = "C_DSMT_TN_FRSOU";
        private const string FunctionColumnName = "C_DSMT_TN_FUNC";
        private const string LegalVehicleIdColumnName = "C_DSMT_TN_LVID";
        private const string LegalVehicleDescriptionColumnName = "LVID_PN_DESCR";
        private const string ManagedGeographyColumnName = "C_DSMT_TN_MGEO";
        private const string ManagedSegmentIdColumnName = "C_DSMT_TN_MSEG";
        private const string ExpenseCodeColumnName = "EXPENSECD";
        private const string CorpCodeColumnName = "CORPCODE";

        //private const string GocAttributesColumnName = "GOC ATTRIBUTE MASTER.GOC ID";
        //private const string GocLongDescriptionColumnName = "GOC LONG DESCRIPTION";
        //private const string StatusColumnName = "GOC STATUS";
        //private const string UpdatedDateColumnName = "UPDATED DATE";
        //private const string SidColumnName = "SID";
        //private const string LocalCostCodeColumnName = "LOCAL COST CODE";
        //private const string ManagerIdColumnName = "MANAGER ID";
        //private const string FrsBuColumnName = "DSMT FRS BU";
        //private const string FrsOuColumnName = "DSMT FRS OU";
        //private const string FunctionColumnName = "FUNCTION";
        //private const string LevelIdColumnName = "LVID";
        //private const string LevelDescriptionColumnName = "LVID LONG DESCRIPTION";
        //private const string ManagedGeographyColumnName = "MANAGED GEOGRAPHY";
        //private const string ManagedSegmentIdColumnName = "MANAGED SEGMENT ID";
        //private const string ExpenseCodeColumnName = "EXPENSE CODE";
        //private const string CorpCodeColumnName = "CORP CODE";

        public static DateTime FileReferenceDate { get; set; }

        #region Goc Codes

        private static readonly List<Definition<Goc>> GocCodesDefinitions = new List<Definition<Goc>>
            {
                new Definition<Goc> { ModelProperty = x => x.GocAttribute, MaxLength = 10, Required = true, ColumnName = GocAttributesColumnName, CustomConversionMethod = ValidateGocAttributesGocCodes},
                new Definition<Goc> { ModelProperty = x => x.ExpenseCode, MaxLength = 10, Required = true, ColumnName = ExpenseCodeColumnName},
                new Definition<Goc> { ModelProperty = x => x.CorpCode, MaxLength = 10, Required = true, ColumnName = CorpCodeColumnName},
            };

        private static object ValidateGocAttributesGocCodes(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            if (string.IsNullOrEmpty(value))
            {
                error = "Field GOC ATTRIBUTES is required";
                return null;
            }

            model.LastUpdate = DateTime.Now;

            model = CacheFacade.Get(SelectAll<Goc>).OrderByDescending(x => x.ReferenceDate).FirstOrDefault(x => x.GocAttribute == value);

            return value;
        }

        public static ImportHistory ImportGocCodes(string path)
        {
            //CacheFacade.Refresh(SelectAll<Goc>);
            List<Error> erros = null;
            var gocCodes = Conversion.ConvertFromExcel(GocCodesDefinitions, path, ref erros);

            if (gocCodes != null && gocCodes.Any())
                gocCodes.First().Save(gocCodes, 500);

            return ImportHistory.SaveHistory(erros, path);
        }

        #endregion

        #region DSMT Import

        private static readonly List<Definition<Goc>> Definitions = new List<Definition<Goc>>
            {
                new Definition<Goc> { ModelProperty = x => x.GocAttribute, MaxLength = 10, Required = true, ColumnName = GocAttributesColumnName, CustomConversionMethod = ValidateGocAttributes},
                new Definition<Goc> { ModelProperty = x => x.LongDescription, MaxLength = 250, Required = true, ColumnName = GocLongDescriptionColumnName},
                new Definition<Goc> { ModelProperty = x => x.Status, MaxLength = 1, Required = true, ColumnName = StatusColumnName},
                new Definition<Goc> { ModelProperty = x => x.UpdatedDate, ColumnName = UpdatedDateColumnName, Culture = new CultureInfo("pt-BR")},
                new Definition<Goc> { ModelProperty = x => x.Sid, MaxLength = 10, CustomConversionMethod = ValidateSID, Required = true, ColumnName = SidColumnName},
                new Definition<Goc> { ModelProperty = x => x.LocalCostCode, MaxLength = 10, Required = true, ColumnName = LocalCostCodeColumnName},
                new Definition<Goc> { ModelProperty = x => x.Manager, Required = true,ColumnName = ManagerIdColumnName, CustomConversionMethod = ValidateManagerId},
                new Definition<Goc> { ModelProperty = x => x.FrsBu,Required = true, ColumnName = FrsBuColumnName, CustomConversionMethod = ValidateFrsBu},
                new Definition<Goc> { ModelProperty = x => x.FrsOu, Required = true, ColumnName = FrsOuColumnName, CustomConversionMethod = ValidateFrsOu},
                new Definition<Goc> { ModelProperty = x => x.Function, MaxLength = 6, Required = true, ColumnName = FunctionColumnName, CustomConversionMethod = ValidateFunction},
                new Definition<Goc> { ModelProperty = x => x.LegalVehicleId, MaxLength = 10, Required = true, ColumnName = LegalVehicleIdColumnName , CustomConversionMethod = ValidateLvid},
                new Definition<Goc> { ModelProperty = x => x.Country, MaxLength = 5, Required = true, ColumnName = ManagedGeographyColumnName, CustomConversionMethod = ValidateCountry},
                new Definition<Goc> { ModelProperty = x => x.ManagedGeography, MaxLength = 5, Required = true, ColumnName = ManagedGeographyColumnName, CustomConversionMethod = ValidateManagedGeography},
                new Definition<Goc> { ModelProperty = x => x.ManagedSegment, Required = true, ColumnName = ManagedSegmentIdColumnName , CustomConversionMethod = ValidateManagedSegment}
            };

        private static object ValidateGocAttributes(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            if (string.IsNullOrEmpty(value))
            {
                error = "Field GOC ATTRIBUTES is required";
                return null;
            }

            model.ReferenceDate = FileReferenceDate;
            model.LastUpdate = DateTime.Now;

            var domain = CacheFacade.Get(SelectAll<Goc>).OrderByDescending(x => x.ReferenceDate).FirstOrDefault(x => x.GocAttribute == value);

            if (domain != null)
            {
                if (domain.ReferenceDate == FileReferenceDate)
                {
                    model = domain;
                    return value;
                }

                try
                {
                    var gocLongDescription = fields[headers.IndexOf(Conversion.TransformColumnName(GocLongDescriptionColumnName))];
                    var status = fields[headers.IndexOf(Conversion.TransformColumnName(StatusColumnName))];
                    var sid = fields[headers.IndexOf(Conversion.TransformColumnName(SidColumnName))];
                    var localCostCode = fields[headers.IndexOf(Conversion.TransformColumnName(LocalCostCodeColumnName))];
                    var managerId = fields[headers.IndexOf(Conversion.TransformColumnName(ManagerIdColumnName))];
                    var frsBu = int.Parse(fields[headers.IndexOf(Conversion.TransformColumnName(FrsBuColumnName))]);
                    var frsOu = int.Parse(fields[headers.IndexOf(Conversion.TransformColumnName(FrsOuColumnName))]);
                    var function = fields[headers.IndexOf(Conversion.TransformColumnName(FunctionColumnName))];
                    var levelId = fields[headers.IndexOf(Conversion.TransformColumnName(LegalVehicleIdColumnName))];
                    var managedGeography = fields[headers.IndexOf(Conversion.TransformColumnName(ManagedGeographyColumnName))];
                    var managedSegmentId = int.Parse(fields[headers.IndexOf(Conversion.TransformColumnName(ManagedSegmentIdColumnName))]);

                    if (domain.LongDescription == gocLongDescription && domain.Status == status && domain.Sid.Description == sid
                        && domain.LocalCostCode == localCostCode && domain.Manager == managerId && domain.FrsBu.Code == frsBu
                        && domain.FrsOu.Code == frsOu && domain.Function.Code == function && domain.LegalVehicle.Code == levelId
                        && domain.Country.Abbreviation == managedGeography && domain.ManagedSegment.Code == managedSegmentId)
                    {
                        model = domain;
                    }
                    else
                    {
                        model.Corinth = domain.Corinth;
                        model.Project = domain.Project;
                        model.OUC = domain.OUC;
                        model.OTHead = domain.OTHead;

                        //if (domain.RuleId != null
                        //    && (!domain.Rule.RuleConditions.Any(x => x.Type == (int)DatabaseEnums.RuleType.ManagedSegment) || managedSegmentId == domain.ManagedSegment.Code)
                        //    && (!domain.Rule.RuleConditions.Any(x => x.Type == (int)DatabaseEnums.RuleType.Function) || function == domain.Function.Code)
                        //    && (!domain.Rule.RuleConditions.Any(x => x.Type == (int)DatabaseEnums.RuleType.Country) || managedGeography == domain.ManagedGeography.Code)
                        //    && (!domain.Rule.RuleConditions.Any(x => x.Type == (int)DatabaseEnums.RuleType.OU) || frsOu == domain.FrsOu.Code)
                        //    && (!domain.Rule.RuleConditions.Any(x => x.Type == (int)DatabaseEnums.RuleType.LVID) || levelId == domain.LegalVehicle.Code))
                        //{
                        //    model.Rule = domain.Rule;
                        //}
                    }
                }
                catch (Exception)
                {
                    return value;
                }
            }

            model.LastUpdate = DateTime.Now;

            return value;
        }

        private static object ValidateManagedGeography(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            if (string.IsNullOrEmpty(value))
            {
                error = "The field Managed Geography can not be null";
                return Conversion.Validation.Failed;
            }

            //var domain = new ManagedGeography().SelectByCode(int.Parse(value));
            var domain = CacheFacade.Get(ManagedGeography.SelectAll<ManagedGeography>).OrderByDescending(x => x.ReferenceDate).FirstOrDefault(x => x.Code == value);

            if (domain == null)
            {
                error = string.Format("The Managed Geography ID {0} wasn't found.", value);
                return Conversion.Validation.Failed;
            }

            model.ManagedGeography = domain;

            return Conversion.Validation.Validated;
        }

        private static object ValidateManagedSegment(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            if (string.IsNullOrEmpty(value))
            {
                error = "The field Managed Segment can not be null";
                return Conversion.Validation.Failed;
            }

            //var domain = new ManagedSegment().SelectByCode(int.Parse(value));
            var domain = CacheFacade.Get(ManagedSegment.SelectAll<ManagedSegment>).OrderByDescending(x => x.ReferenceDate).FirstOrDefault(x => x.Code == int.Parse(value));

            if (domain == null)
            {
                error = string.Format("The Managed Segment ID {0} wasn't found.", value);
                return Conversion.Validation.Failed;
            }

            model.ManagedSegment = domain;

            return Conversion.Validation.Validated;
        }

        private static object ValidateCountry(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            var managedGeographyDescription = fields[headers.IndexOf(Conversion.TransformColumnName(ManagedGeographyColumnName))];

            if (string.IsNullOrEmpty(value) || string.IsNullOrEmpty(managedGeographyDescription))
            {
                error = "The field Managed Geography and Managed Geography Description can not be null";
                return Conversion.Validation.Failed;
            }

            var domain = CacheFacade.Get(Country.SelectAll<Country>).FirstOrDefault(x => x.Abbreviation == value);

            if (domain != null)
                model.Country = domain;
            else
                model.Country = new Country
                {
                    Abbreviation = value,
                    Description = managedGeographyDescription
                };

            return Conversion.Validation.Validated;
        }

        private static object ValidateLvid(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            var levelLongDescription = fields[headers.IndexOf(Conversion.TransformColumnName(LegalVehicleDescriptionColumnName))];

            if (string.IsNullOrEmpty(value) || string.IsNullOrEmpty(levelLongDescription))
            {
                error = "The field LVID and LVID Long Description can not be null";
                return Conversion.Validation.Failed;
            }

            var domain = CacheFacade.Get(LegalVehicle.SelectAll<LegalVehicle>).FirstOrDefault(x => x.Code == value);

            if (domain != null)
                model.LegalVehicle = domain;
            else
                model.LegalVehicle = new LegalVehicle
                {
                    Description = levelLongDescription,
                    Code = value,
                    LastUpdate = DateTime.Now,
                    ReferenceDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)
                };

            return Conversion.Validation.Validated;
        }

        private static object ValidateManagerId(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            value = value.Trim();
            return value.Length > 7 ? value.Substring(0, 7) : value;
        }

        private static object ValidateFunction(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            if (string.IsNullOrEmpty(value))
            {
                error = "The field Function can not be null";
                return Conversion.Validation.Failed;
            }

            //var domain = new Function().SelectByCode(value);
            var domain = CacheFacade.Get(Function.SelectAll<Function>).OrderByDescending(x => x.ReferenceDate).FirstOrDefault(x => x.Code == value);

            if (domain == null)
            {
                error = string.Format("The function ID {0} wasn't found.", value);
                return Conversion.Validation.Failed;
            }

            model.Function = domain;

            return Conversion.Validation.Validated;
        }

        private static object ValidateFrsOu(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            var description = fields[headers.IndexOf(Conversion.TransformColumnName(FrsOuColumnName))];

            if (string.IsNullOrEmpty(value) || string.IsNullOrEmpty(description))
            {
                error = "The field FRS OU and FRS OU Long Description can not be null";
                return Conversion.Validation.Failed;
            }

            //var domain = new FrsOu().SelectByCode(int.Parse(value));
            var domain = CacheFacade.Get(FrsOu.SelectAll<FrsOu>).FirstOrDefault(x => x.Code == int.Parse(value));

            if (domain != null)
                model.FrsOu = domain;
            else
                model.FrsOu = new FrsOu
                {
                    Description = description,
                    Code = int.Parse(value),
                    LastUpdate = DateTime.Now,
                    ReferenceDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)
                };

            return Conversion.Validation.Validated;
        }

        private static object ValidateFrsBu(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            //var description = fields[headers.IndexOf("FRSBULONGDESCRIPTION")];
            var description = fields[headers.IndexOf(Conversion.TransformColumnName(FrsBuColumnName))];

            if (string.IsNullOrEmpty(value) || string.IsNullOrEmpty(description))
            {
                error = "The field FRS BU and FRS BU Long Description can not be null";
                return Conversion.Validation.Failed;
            }

            //var domain = new FrsBu().SelectByCode(int.Parse(value));
            var domain = CacheFacade.Get(FrsBu.SelectAll<FrsBu>).FirstOrDefault(x => x.Code == int.Parse(value));

            model.FrsBu = domain ?? (model.FrsBu = new FrsBu
                {
                    Description = description,
                    Code = int.Parse(value),
                    LastUpdate = DateTime.Now,
                    ReferenceDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)
                });

            return Conversion.Validation.Validated;
        }

        private static object ValidateSID(ref Goc model, string value, List<string> fields, ref string error, List<string> headers = null)
        {
            if (string.IsNullOrEmpty(value))
            {
                error = "The field SID can not be null";
                return Conversion.Validation.Failed;
            }

            //var sid = new Sid().SelectByDescription(value);
            var sid = CacheFacade.Get(Sid.SelectAll<Sid>).FirstOrDefault(x => x.Description == value);

            if (sid != null)
                model.Sid = sid;
            else
                model.Sid = new Sid
                    {
                        Description = value,
                        LastUpdate = DateTime.Now,
                        ReferenceDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1)
                    };

            return Conversion.Validation.Validated;
        }

        public static ImportHistory ImportGocs(string path, DateTime referenceDate)
        {
            List<Error> errors = null;
            FileReferenceDate = referenceDate;

            var dataTable = Conversion.ReadExcel(path, ref errors);

            Country.Save(Conversion.Convert(Country.Definitions, dataTable, ref errors));
            FrsBu.Save(Conversion.Convert(FrsBu.Definitions, dataTable, ref errors));
            FrsOu.Save(Conversion.Convert(FrsOu.Definitions, dataTable, ref errors));
            LegalVehicle.Save(Conversion.Convert(LegalVehicle.Definitions, dataTable, ref errors));
            Sid.Save(Conversion.Convert(Sid.Definitions, dataTable, ref errors));

            var gocs = Conversion.Convert(Definitions, dataTable, ref errors);

            if (gocs != null && gocs.Any())
                gocs.First().Save(gocs, 1500);

            CacheFacade.Clear(typeof(Goc));
            return ImportHistory.SaveHistory(errors, path);

            //var gocs = Conversion.ConvertFromExcel(Definitions, path, ref errors).Distinct(new GocComparer()).ToList();



            //if (gocs != null && gocs.Any())
            //{
            //    SaveCountry(gocs);
            //    SaveSid(gocs);
            //    SaveLevel(gocs);
            //    SaveFrsOu(gocs);
            //    SaveFrsBu(gocs);
            //    gocs.First().Save(gocs, 1500);
            //}

            //CacheFacade.Clear(typeof(Goc));
            //return ImportHistory.SaveHistory(errors, path);
        }

        //private static void SaveCountry(List<Goc> gocs)
        //{
        //    var countries = gocs.Select(x => x.Country).Distinct(new CountryComparer()).Where(x => x != null).ToList();

        //    if (!countries.Any()) return;
        //    countries.First().Save(countries, 100);

        //    foreach (var goc in gocs.Where(y => y.Country.Id == 0))
        //    {
        //        //goc.CountryId = countries.First(y => y.Description == goc.Country.Description).Id;
        //        //goc.Country = null;
        //        goc.Country = countries.First(y => y.Description == goc.Country.Description);
        //    }
        //}

        //private static void SaveFrsOu(List<Goc> gocs)
        //{
        //    var frsous = gocs.Select(x => x.FrsOu).Distinct(new FrsOuComparer()).Where(x => x != null).ToList();
        //    frsous.ForEach(x => x.LastUpdate = DateTime.Now);

        //    if (!frsous.Any()) return;
        //    frsous.First().Save(frsous, 250);

        //    foreach (var goc in gocs.Where(y => y.FrsOu.Id == 0))
        //    {
        //        //goc.FrsOuId = frsous.First(y => goc.FrsOu != null && y.Description == goc.FrsOu.Description).Id;
        //        //goc.FrsOu = null;
        //        goc.FrsOu = frsous.First(y => y.Description == goc.FrsOu.Description);
        //    }
        //}

        //private static void SaveFrsBu(List<Goc> gocs)
        //{
        //    var frsbus = gocs.Select(x => x.FrsBu).Distinct(new FrsBuComparer()).Where(x => x != null).ToList();
        //    frsbus.ForEach(x => x.LastUpdate = DateTime.Now);
        //    if (!frsbus.Any()) return;
        //    frsbus.First().Save(frsbus, 250);

        //    foreach (var goc in gocs.Where(y => y.FrsBu.Id == 0))
        //    {
        //        //goc.FrsBuId = frsbus.First(y => goc.FrsBu != null && y.Description == goc.FrsBu.Description).Id;
        //        //goc.FrsBu = null;
        //        goc.FrsBu = frsbus.First(y => goc.FrsBu != null && y.Description == goc.FrsBu.Description);
        //    }
        //}

        //private static void SaveLevel(List<Goc> gocs)
        //{
        //    var levels = gocs.Select(x => x.LegalVehicle).Distinct(new LegalVehicleComparer()).Where(x => x != null).ToList();
        //    levels.ForEach(x => x.LastUpdate = DateTime.Now);
        //    if (!levels.Any()) return;
        //    levels.First().Save(levels, 100);

        //    foreach (var goc in gocs.Where(x => x.LegalVehicle.Id == 0 && x.LegalVehicle != null))
        //    {
        //        //goc.LevelId = levels.First(y => y.Code == goc.LegalVehicle.Code).Id;
        //        //goc.LegalVehicle = null;
        //        goc.LegalVehicle = levels.First(y => y.Code == goc.LegalVehicle.Code);
        //    }
        //}

        //private static void SaveSid(List<Goc> gocs)
        //{
        //    var sids = gocs.Select(x => x.Sid).Distinct(new SidComparer()).Where(x => x != null).ToList();
        //    sids.ForEach(x => x.LastUpdate = DateTime.Now);
        //    if (!sids.Any()) return;
        //    sids.First().Save(sids, 100);

        //    foreach (var goc in gocs.Where(x => x.Sid.Id == 0 && x.Sid != null))
        //    {
        //        goc.Sid = sids.First(y => y.Description == goc.Sid.Description);
        //        //goc.SidId = sids.First(y => y.Description == goc.Sid.Description).Id;
        //        //goc.Sid = null;
        //    }
        //}

        #endregion

        #endregion

        public IEnumerable<Goc> GetGocsByReferenceDate(DateTime referenceDate)
        {
            return SelectAll<Goc>().Where(x => x.ReferenceDate <= referenceDate)
                        .GroupBy(c => c.GocAttribute)
                        .Select(g => g.OrderByDescending(c => c.ReferenceDate).First())
                        .Select(x => x);
        }

        public IEnumerable<Goc> GetGocsApplyRuleByReferenceDate(DateTime referenceDate)
        {
            return SelectAll<Goc>().Where(x => x.ReferenceDate <= referenceDate)
                       .GroupBy(c => c.GocAttribute)
                       .Select(g => g.OrderByDescending(c => c.ReferenceDate).First())
                       .Select(x => x);

            //return SelectAll<Goc>().Where(x => x.ReferenceDate == referenceDate 
            //    && (x.Rule == null || (!x.Rule.Exception && x.Rule.RuleConditions.Any()) || !x.Rule.Active)).ToList();
            //return SelectAll<Goc>().Where(x => x.ReferenceDate <= referenceDate 
            //    && (x.Rule == null || (x.Rule != null && ((!x.Rule.Exception && x.Rule.RuleConditions.Any()) || !x.Rule.Active))))
            //           .GroupBy(c => c.GocAttribute)
            //           .Select(g => g.OrderByDescending(c => c.ReferenceDate).First())
            //           .Select(x => x).ToList();
        }

        public List<Goc> GetGocsByDeck(List<string> decks)
        {
            var ruleResults = RuleResult.SelectAll<RuleResult>()//.Select(x => x.Rule.RuleResults
                //.Where(y => y.TypeRuleResult.Type == (int)DatabaseEnums.TypeResultType.DeckRegional1 && decks.Contains(y.TypeRuleResult.Value))
                .Where(y => y.TypeRuleResult.Type == (int)DatabaseEnums.TypeResultType.OTFunctionL4 && decks.Contains(y.TypeRuleResult.Value))
                .ToList();

            var ruleIds = ruleResults.Select(y => y.RuleId).Distinct().ToList();

            return SelectAll<Goc>().Where(x => x.GocRules.Any(y => y.ReferenceDate.Month == DateTime.Today.Month && y.ReferenceDate.Year == DateTime.Today.Year)
                && x.GocRules.Any(y => ruleIds.Contains(y.RuleId))).ToList();
        }

        public List<Goc> GetGocsByUpdatedMonth(int month)
        {
            return SelectAll<Goc>().Where(x => x.LastUpdate.Month == month).ToList();
        }

        public List<Goc> SelectGocsRuleException()
        {
            return SelectAll<Goc>().Where(x => !x.GocRules.Any(y => y.ReferenceDate == new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1))).ToList();
        }

        public List<Goc> GetGocByOTFlag(string name)
        {
            return SelectAll<Goc>().Where(x => x.GetRuleResult(DatabaseEnums.TypeResultType.OTFunctionL0) == name).ToList();
        }

        public IEnumerable<Goc> Search(Goc goc)
        {
            return SelectAll<Goc>().Where(x => x.GocAttribute.Contains(goc.GocAttribute));
        }

        public void Save(IList<Goc> entities, int batch, decimal eachPercent, decimal startPercent, IDictionary<string, decimal> processStatus, string id, object syncRoot)
        {
            foreach (var entity in entities.Distinct(new GocComparer()))
            {
                lock (syncRoot)
                    processStatus[id] = decimal.Divide(entities.IndexOf(entity), eachPercent) + startPercent;

                try
                {
                    switch (entity.EntityState)
                    {
                        case EntityState.Added:
                        case EntityState.Detached:
                            Context.Instance.AddToGocs(entity);
                            break;
                        case EntityState.Unchanged:
                            continue;
                        case EntityState.Deleted:
                            Delete();
                            continue;
                    }

                    if (entities.IndexOf(entity) % batch != 0 && entities.IndexOf(entity) != entities.Count() - 1)
                        continue;

                    Context.SaveChanges();
                }
                catch (InvalidOperationException)
                {
                    Context.Instance.Attach(entity);
                    Context.SaveChanges();
                }
                catch (UpdateException)
                {
                    Context.Instance.Detach(entity);
                    throw;
                }
            }
        }

        public string GetRuleResult(DatabaseEnums.TypeResultType type, DateTime? referenceDate = null)
        {
            if (referenceDate == null) referenceDate = DateTime.Today;

            if (GocRules == null) return "-";

            if (!referenceDate.HasValue)
                referenceDate = new DateTime(DateTime.Today.Year, DateTime.Today.Month, 1);

            var rules = GocRules.Where(x => x.ReferenceDate.Year == referenceDate.Value.Year && x.ReferenceDate.Month == referenceDate.Value.Month)
                .OrderByDescending(x => x.Order).Select(x => x.Rule);

            foreach (var rule in rules)
            {
                if (rule.RuleResults.Where(x => x.TypeRuleResult.Active).FirstOrDefault(x => x.TypeRuleResult.Type == (short)type) == null) continue;

                var result = rule.RuleResults.Where(x => x.TypeRuleResult.ReferenceDate <= referenceDate.Value ||
                            x.TypeRuleResult.ReferenceDate.Year == referenceDate.Value.Year && x.TypeRuleResult.ReferenceDate.Month == referenceDate.Value.Month)
                            .OrderByDescending(x => x.Id).FirstOrDefault(x => x.TypeRuleResult.Type == (short)type).TypeRuleResult.Value;

                if (string.IsNullOrWhiteSpace(result))
                    continue;

                return result;
            }

            return "-";

            //return rule != null && GocRules.Rule.RuleResults.Where(x => x.TypeRuleResult.Active).FirstOrDefault(x => x.TypeRuleResult.Type == (short)type) != null
            //            ? GocRules.Rule.RuleResults.Where(x => x.TypeRuleResult.ReferenceDate <= referenceDate.Value ||
            //                x.TypeRuleResult.ReferenceDate.Year == referenceDate.Value.Year && x.TypeRuleResult.ReferenceDate.Month == referenceDate.Value.Month)
            //                .OrderByDescending(x => x.Id).FirstOrDefault(x => x.TypeRuleResult.Type == (short)type).TypeRuleResult.Value
            //            : "-";
        }

        public string GenerateReport(DateTime referenceDate, string pathToSave)
        {
            var model = new Goc().GetGocsByReferenceDate(referenceDate);
            var templateTemp = @"c:\temp\GOCTemplate.xlsx";
            File.Copy(@"\\gruvnascti0016.wlb.lac.nsroot.net\DATA_GRP1\FTOOLS\Apps\GOCAttributes\GOCTemplate.xlsx", templateTemp, true);

            using (var excelPackage = new ExcelPackage(new FileInfo(templateTemp), true))
            //using (var excelPackage = new ExcelPackage(new MemoryStream(), 
            //    new MemoryStream(File.ReadAllBytes(@"\\gruvnascti0016.wlb.lac.nsroot.net\DATA_GRP1\FTOOLS\Apps\GOCAttributes\GOCTemplate.xlsx"))))
            {
                var worksheet = excelPackage.Workbook.Worksheets[1];

                int row = 2;
                var types = Enum.GetValues(typeof(DatabaseEnums.TypeResultType)).Cast<int>().ToList();

                foreach (int item in types)
                {
                    DatabaseEnums.TypeResultType enumValue;
                    Enum.TryParse<DatabaseEnums.TypeResultType>(item.ToString(), out enumValue);

                    worksheet.Cells[row, types.IndexOf(item) + 45].Value = enumValue.GetDescription();
                }

                row++;

                foreach (var item in model)
                {
                    worksheet.Cells[row, 1].Value = item.GocAttribute;

                    int gocAsNumber;

                    worksheet.Cells[row, 2].Value = int.TryParse(item.GocAttribute, out gocAsNumber) ? gocAsNumber.ToString() : item.GocAttribute;
                    worksheet.Cells[row, 3].Value = item.LongDescription;
                    worksheet.Cells[row, 4].Value = item.ExpenseCode;
                    worksheet.Cells[row, 5].Value = item.CorpCode;
                    worksheet.Cells[row, 6].Value = item.Status;
                    worksheet.Cells[row, 7].Value = item.UpdatedDate;
                    worksheet.Cells[row, 8].Value = item.Sid.Description;
                    worksheet.Cells[row, 9].Value = item.LocalCostCode;
                    worksheet.Cells[row, 10].Value = item.Manager;
                    worksheet.Cells[row, 11].Value = item.FrsBu.Code;
                    worksheet.Cells[row, 12].Value = item.FrsBu.Description;
                    worksheet.Cells[row, 13].Value = item.FrsOu.Code;
                    worksheet.Cells[row, 14].Value = item.FrsOu.Description;
                    worksheet.Cells[row, 15].Value = item.Function.Code;
                    worksheet.Cells[row, 16].Value = item.Function.Description;
                    worksheet.Cells[row, 17].Value = item.LegalVehicle.Code;
                    worksheet.Cells[row, 18].Value = item.LegalVehicle.Description;
                    worksheet.Cells[row, 19].Value = item.ManagedGeography.Code;
                    worksheet.Cells[row, 20].Value = item.ManagedGeography.Description;
                    worksheet.Cells[row, 21].Value = item.ManagedSegment.Code;
                    worksheet.Cells[row, 22].Value = item.ManagedSegment.Description;

                    var managedSegment = item.ManagedSegment;
                    for (int i = item.ManagedSegment.TreeLevel; i >= 0; i--)
                    {
                        worksheet.Cells[row, 22 + i].Value = managedSegment.Description;

                        if (managedSegment.ParentId != null)
                            managedSegment = managedSegment.Parent;
                        else
                            break;
                    }

                    var function = item.Function;

                    for (int i = item.Function.TreeLevel; i >= 0; i--)
                    {
                        worksheet.Cells[row, 35 + i].Value = function.Description;

                        if (function.ParentId != null)
                            function = function.Parent;
                        else
                            break;
                    }

                    var lastIndex = 0;
                    foreach (int type in types)
                    {
                        lastIndex = types.IndexOf(type) + 45;
                        worksheet.Cells[row, lastIndex].Value = item.GetRuleResult((DatabaseEnums.TypeResultType)type, referenceDate);
                    }

                    worksheet.Cells[row, lastIndex + 1].Value = item.OUC;

                    for (int i = 1; i < lastIndex + 1; i++)
                    {
                        if (worksheet.Cells[row, i].Value == null)
                            worksheet.Cells[row, i].Value = "-";
                    }

                    row++;
                }

                var file = Path.Combine(pathToSave, string.Format("GOCAttributes{0}.xlsx", referenceDate.ToString("yyyyMM")));
                //Directory.GetFiles(pathToSave).ToList().ForEach(System.IO.File.Delete);

                var bin = excelPackage.GetAsByteArray();
                System.IO.File.WriteAllBytes(file, bin);

                return file;
            }
        }
    }

    public class GocComparer : IEqualityComparer<Goc>
    {
        public bool Equals(Goc x, Goc y)
        {
            if (ReferenceEquals(x, y)) return true;
            if (ReferenceEquals(x, null) || ReferenceEquals(y, null))
                return false;

            return x.GocAttribute == y.GocAttribute && x.Id == y.Id && x.Corinth == y.Corinth && x.CorpCode == y.CorpCode
                && x.CountryId == y.CountryId && x.ExpenseCode == y.ExpenseCode && x.FrsBuId == y.FrsBuId && x.FrsOuId == y.FrsOuId
                && x.FunctionId == y.FunctionId && x.LegalVehicleId == y.LegalVehicleId && x.LongDescription == y.LongDescription
                && x.ManagedSegmentId == y.ManagedSegmentId && x.SidId == y.SidId && x.Status == y.Status
                && x.UpdatedDate == y.UpdatedDate;
        }

        public int GetHashCode(Goc item)
        {
            if (ReferenceEquals(item, null)) return 0;

            int hashDescription = item.GocAttribute == null ? 0 : item.GocAttribute.GetHashCode();
            int hashId = item.Id.GetHashCode();

            return hashDescription ^ hashId;
        }
    }
}
